/*
 *所有关于Statistical_Reports类的业务代码应在此处编写
*可使用repository.调用常用方法，获取EF/Dapper等信息
*如果需要事务请使用repository.DbContextBeginTransaction
*也可使用DBServerProvider.手动获取数据库相关信息
*用户信息、权限、角色等使用UserContext.Current操作
*Statistical_ReportsService对增、删、改查、导入、导出、审核业务代码扩展参照ServiceFunFilter
*/
using VOL.Core.BaseProvider;
using VOL.Core.Extensions.AutofacManager;
using VOL.Entity.DomainModels;
using System.Linq;
using VOL.Core.Utilities;
using System.Linq.Expressions;
using VOL.Core.Extensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Dhj.IRepositories;
using System.Net.Mime;
using VOL.Core.DBManager;
using VOL.Dhj.Repositories;
using System.Text;
using OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup;
using System.Net;
using System.Collections;
using System;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using SkiaSharp;

//using Nager.Date;
//using Nager.Date.Model;

namespace VOL.Dhj.Services
{
    public partial class Statistical_ReportsService
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly IStatistical_ReportsRepository _repository;//访问数据库
        private readonly IAttendance_RecordsRepository _attendanceRecordsRepository;

        [ActivatorUtilitiesConstructor]
        public Statistical_ReportsService(
            IStatistical_ReportsRepository dbRepository,
            IHttpContextAccessor httpContextAccessor,
            IAttendance_RecordsRepository attendance_recordsRepository
            )
        : base(dbRepository)
        {
            _httpContextAccessor = httpContextAccessor;
            _repository = dbRepository;
            //多租户会用到这init代码，其他情况可以不用
            //base.Init(dbRepository);
            _attendanceRecordsRepository = attendance_recordsRepository;
        }

        WebResponseContent webResponse = new WebResponseContent();
        public override WebResponseContent Add(SaveModel saveDataModel)
        {

            AddOnExecute = (SaveModel saveModel) =>
            {

                return webResponse.OK();
            };

            AddOnExecuting = (Statistical_Reports statistical_Reports, object list) =>
            {
                //明细表对象
                List<Statistical_Reports> orderLists = list as List<Statistical_Reports>;

                return webResponse.OK();
            };
            return base.Add(saveDataModel);
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="files"></param>
        /// <returns></returns>
        public override WebResponseContent Import(List<IFormFile> files)
        {
            var dapper = DBServerProvider.SqlDapper;
            //导入保存前处理(可以对list设置新的值)
            ImportOnExecuting = (List<Statistical_Reports> list) =>
            {

                string s = "TRUNCATE TABLE Statistical_Reports;";
                List<Statistical_Reports> counts = dapper.QueryList<Statistical_Reports>(s, true);
                return webResponse.OK();
            };

            //导入后处理(已经写入到数据库了)
            ImportOnExecuted = (List<Statistical_Reports> list) =>
            {
                DailyUpdates();
                return webResponse.OK();
            };
            return base.Import(files);
        }


        //每日更新
        public void DailyUpdates()
        {
            //dapper访问数据库
            var dapper = DBServerProvider.SqlDapper;
            try
            {
                dapper.BeginTrans();

                //从用户表中将所有用户的基础信息
                string sqlPerson = "SELECT * FROM Division_Person ";
                List<Division_Person> listPerson = dapper.QueryList<Division_Person>(sqlPerson, true);

                //从导入的表中获取数据               
                string sql = "SELECT * from Statistical_Reports as a order by a.CheckInTime";
                List<Statistical_Reports> list = dapper.QueryList<Statistical_Reports>(sql, true);

                //查询导入表签到日期的天数
                string s = "SELECT DISTINCT CheckInDate FROM Statistical_Reports ORDER BY CheckInDate ASC;";
                List<Statistical_Reports> counts = dapper.QueryList<Statistical_Reports>(s, true);

                string ss = "SELECT DISTINCT YEAR(CheckInDate) AS StaffName, MONTH(CheckInDate) AS StaffAccount FROM Statistical_Reports";
                List<Statistical_Reports> NYs = dapper.QueryList<Statistical_Reports>(ss, true);

                //查询导入表最大签到日期
                string sasd = " SELECT DAY(MAX(CheckInDate)) AS Office FROM Statistical_Reports;";
                List<Statistical_Reports> T = dapper.QueryList<Statistical_Reports>(sasd, true);
               


                //获取签到表中的年月
                for (int t = 0; t < NYs.Count; t++)
                {
                    int nian = NYs[t].StaffName.ToInt();
                    int yue = NYs[t].StaffAccount.ToInt();
                    int day = T[0].Office.ToInt();

                    // 获取当前日期和时间
                    DateTime now = DateTime.Now;
                    // 获取当前年份
                    int year = now.Year;
                    // 获取当前月份
                    int month = now.Month;



                    //判断导入表数据的年份在当前年份之前
                    if (year > nian)
                    {
                        //在当前月份之前                       
                            //获取该年月下有多少天
                            int daysInMonth = DateTime.DaysInMonth(nian, yue);

                            for (int i = 0; i < daysInMonth; i++)
                            {
                                int ri = i + 1;
                                DateTime date = new DateTime(nian, yue, ri);
                                string formattedDate = date.ToString("yyyy-MM-dd");

                                int flag = 0;//今天是否为休息日
                                DateTime nn = (DateTime)formattedDate.ToDateTime();

                                string nowDaten = string.Format("{0:yyyy-MM}", nn) + "%";
                                string nowDateTimen = string.Format("{0:yyyy-MM-dd}", nn) + "%";

                                //从假期表中获取数据
                                string sql2 = "SELECT * FROM Holiday WHERE CONVERT(VARCHAR, StartTime, 23) LIKE @nowDaten";
                                List<Holiday> list2 = dapper.QueryList<Holiday>(sql2, new { nowDaten });

                                List<Attendance_Records> lista = new List<Attendance_Records>();


                                string delete_sql = "delete from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                dapper.ExcuteNonQuery(delete_sql, new { nowDateTime = nowDateTimen });

                                foreach (Division_Person item in listPerson)
                                {
                                    Attendance_Records a = new Attendance_Records();
                                    a.Division = item.Division;//销售事业部
                                    a.Manager = item.BusinessManager;//业务经理
                                    a.Attendance_date = date;//出勤日期                    
                                    lista.Add(a);
                                }
                                //dapper.BulkInsert<Attendance_Records>(lista);

                                //根据导入表的数据对每日表进行更新
                                foreach (Statistical_Reports item in list)
                                {
                                    if (item.CheckInDate.ToDateTime() == nn)
                                    {
                                        //根据员工姓名和签到日期查询每日表的数据
                                        //string sql1 = "SELECT * from Attendance_Records where Manager = @staffName AND CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                        //List<Attendance_Records> list1 = dapper.QueryList<Attendance_Records>(sql1, new { item.StaffName, nowDateTime = nowDateTimen });

                                        foreach (Attendance_Records ar in lista)
                                        {
                                            if (ar.Manager.Equals(item.StaffName) && ar.Attendance_date.ToDateTime().Date == nn.Date)
                                            {

                                                if (ar != null)
                                                {
                                                    if (ar.BusinessHour != null && !"".Equals(ar.BusinessHour))
                                                    {
                                                        string time01 = ar.BusinessHour;//上班时间
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);


                                                        //将每日表中的上班时间和导入表中的签到时间进行比较                       
                                                        // 将时间字符串转换为DateTime类型
                                                        DateTime dateTime1 = DateTime.Parse(time01);
                                                        DateTime dateTime2 = DateTime.Parse(item.CheckInTime);
                                                        // 获取时间的时分秒部分
                                                        TimeSpan timeSpan1 = dateTime1.TimeOfDay;//上班时间
                                                        TimeSpan timeSpan2 = dateTime2.TimeOfDay;//签到时间
                                                                                                 // 比较两个时间的时分秒部分
                                                        int result = TimeSpan.Compare(timeSpan1, timeSpan2);
                                                        //如果上班时间大于签到时间，则将签到时间赋给上班时间
                                                        if (result > 0)
                                                        {
                                                            ar.BusinessHour = item.CheckInTime;
                                                            //TimeSpan difference = time2.Subtract(time1);
                                                            //string sql23 = "Update Attendance_Records set BusinessHour = @businessHour  where AttendanceId = @attendanceId ";
                                                            //dapper.ExcuteNonQuery(sql23, new { businessHour = person_time.BusinessHour, attendanceId = person_time.AttendanceId });

                                                        }
                                                        else
                                                        {
                                                            ar.ClosingHour = item.CheckInTime;//下班时间
                                                            ar.AttendanceLocation = item.CheckInLocation;//出勤地点       
                                                            TimeSpan difference = DateTime.Parse(ar.ClosingHour).Subtract(DateTime.Parse(ar.BusinessHour));
                                                            string differenceString = difference.ToString();
                                                            ar.AttendanceTime = differenceString;//出勤时间

                                                            //签到地点
                                                            string str = item.CheckInLocation;
                                                            Dictionary<string, string> address = Address(str);
                                                            if (address.ContainsKey("province"))
                                                            {//获取省的字符串
                                                                string province = address["province"];
                                                            }
                                                            if (address.ContainsKey("city"))
                                                            {//获取市的字符串
                                                                string city = address["city"];
                                                                ar.CheckInLocation = city;//签到地点
                                                            }
                                                            //if (address.ContainsKey("country"))
                                                            //{//获取区县的字符串
                                                            //    string country = address["country"];
                                                            //}
                                                            if (address.ContainsKey("community"))
                                                            {//获取具体位置的字符串
                                                                string community = address["community"];
                                                            }
                                                        }

                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.ClosingHour, x.AttendanceTime }, true);//更新
                                                        //string sql33 = "Update Attendance_Records set ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql33, new { closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                    else
                                                    {

                                                        ar.StaffAccount = item.StaffAccount;//员工账号

                                                        //a.Attendance_date = item.CheckInDate;//出勤日期

                                                        ar.AttendanceLocation = item.CheckInLocation;//出勤地点                

                                                        ar.BusinessHour = item.CheckInTime;//上班时间

                                                        ar.ClosingHour = item.CheckInTime;//下班时间

                                                        //出勤时间  
                                                        string time01 = ar.BusinessHour;
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);
                                                        TimeSpan difference = time2.Subtract(time1);
                                                        string differenceString = difference.ToString();
                                                        ar.AttendanceTime = differenceString;

                                                        ////签到地点
                                                        string str = item.CheckInLocation;
                                                        Dictionary<string, string> address = Address(str);
                                                        if (address.ContainsKey("province"))
                                                        {//获取省的字符串
                                                            string province = address["province"];
                                                        }
                                                        if (address.ContainsKey("city"))
                                                        {//获取市的字符串
                                                            string city = address["city"];
                                                            ar.CheckInLocation = city;//签到地点
                                                        }
                                                        //if (address.ContainsKey("country"))
                                                        //{//获取区县的字符串
                                                        //    string country = address["country"];
                                                        //}
                                                        if (address.ContainsKey("community"))
                                                        {//获取具体位置的字符串
                                                            string community = address["community"];
                                                        }

                                                        //添加签到信息
                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.StaffAccount, x.AttendanceLocation, x.BusinessHour, x.ClosingHour, x.CheckInLocation, x.AttendanceTime }, true);//更新
                                                        //string sql5 = "Update Attendance_Records set StaffAccount=@staffAccount,BusinessHour=@businessHour, ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql5, new { staffAccount = person_time.StaffAccount, businessHour = person_time.BusinessHour, closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                }
                                                break;
                                            }
                                        }




                                    }
                                }
                                dapper.BulkInsert<Attendance_Records>(lista);

                                ////更新分类及是否出差
                                //根据时间查出每日表中当前时间下所有用户的数据
                                string sql22 = "SELECT * from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                List<Attendance_Records> list22 = dapper.QueryList<Attendance_Records>(sql22, new { nowDateTime = nowDateTimen });
                                foreach (Holiday item2 in list2)
                                {
                                    DateTime time3 = DateTime.Parse(item2.StartTime);//假期开始时间
                                    DateTime time4 = DateTime.Parse(item2.EndTime);//假期结束时间
                                    int result1 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time3);
                                    int result2 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time4);
                                    if (result1 >= 0 && result2 <= 0)
                                    {
                                        flag = 1;//今天是休息日
                                    }
                                }

                                //遍历当前时间下的所有的用户数据
                                foreach (Attendance_Records item in list22)
                                {
                                    if (flag == 1)
                                    {
                                        //是休息日且签到地点不为空、出勤地点不在公司和青岛市则为出差
                                        if (!"".Equals(item.CheckInLocation) && item.CheckInLocation != null && !"青岛市".Equals(item.CheckInLocation) && !item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            item.Classify = 2;
                                            item.TravelStatus = "是";
                                        }
                                        else
                                        {
                                            item.Classify = 5;//休息
                                            item.TravelStatus = "否";
                                        }
                                    }


                                    else
                                    {
                                        if ("".Equals(item.CheckInLocation) || item.CheckInLocation == null)
                                        {
                                            item.Classify = 4;
                                            item.TravelStatus = "否";

                                        }
                                        else if ("青岛市".Equals(item.CheckInLocation) || item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            //打卡状态
                                            //1   请假   2   出差  3   出勤   4   未打卡 5   休息                      

                                            //a.Classify = 3;//出勤
                                            item.TravelStatus = "否";//是否出差
                                            item.Classify = 3;
                                        }
                                        else
                                        {
                                            item.TravelStatus = "是";
                                            item.Classify = 2;
                                        }
                                    }

                                    //string sql6 = "Update Attendance_Records set TravelStatus=@travelStatus,Classify=@classify where AttendanceId = @attendanceId ";
                                    //dapper.ExcuteNonQuery(sql6, new { travelStatus = item.TravelStatus, classify = item.Classify, attendanceId = item.AttendanceId });
                                    dapper.ExecuteScalar("UpdateAttendanceRecords", new { item.AttendanceId, item.CheckInLocation, item.TravelStatus, item.Classify }, System.Data.CommandType.StoredProcedure);

                                }



                            }
                        }                        
                    
                   //导入表年份不在当前年份之前
                    else {
                        //在当前月份之前
                        if (month > yue)
                        {
                            //获取该年月下有多少天
                            int daysInMonth = DateTime.DaysInMonth(nian, yue);

                            for (int i = 0; i < daysInMonth; i++)
                            {
                                int ri = i + 1;
                                DateTime date = new DateTime(nian, yue, ri);
                                string formattedDate = date.ToString("yyyy-MM-dd");

                                int flag = 0;//今天是否为休息日
                                DateTime nn = (DateTime)formattedDate.ToDateTime();

                                string nowDaten = string.Format("{0:yyyy-MM}", nn) + "%";
                                string nowDateTimen = string.Format("{0:yyyy-MM-dd}", nn) + "%";

                                //从假期表中获取数据
                                string sql2 = "SELECT * FROM Holiday WHERE CONVERT(VARCHAR, StartTime, 23) LIKE @nowDaten";
                                List<Holiday> list2 = dapper.QueryList<Holiday>(sql2, new { nowDaten });

                                List<Attendance_Records> lista = new List<Attendance_Records>();


                                string delete_sql = "delete from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                dapper.ExcuteNonQuery(delete_sql, new { nowDateTime = nowDateTimen });

                                foreach (Division_Person item in listPerson)
                                {
                                    Attendance_Records a = new Attendance_Records();
                                    a.Division = item.Division;//销售事业部
                                    a.Manager = item.BusinessManager;//业务经理
                                    a.Attendance_date = date;//出勤日期                    
                                    lista.Add(a);
                                }
                                //dapper.BulkInsert<Attendance_Records>(lista);

                                //根据导入表的数据对每日表进行更新
                                foreach (Statistical_Reports item in list)
                                {
                                    if (item.CheckInDate.ToDateTime() == nn)
                                    {
                                        //根据员工姓名和签到日期查询每日表的数据
                                        //string sql1 = "SELECT * from Attendance_Records where Manager = @staffName AND CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                        //List<Attendance_Records> list1 = dapper.QueryList<Attendance_Records>(sql1, new { item.StaffName, nowDateTime = nowDateTimen });

                                        foreach (Attendance_Records ar in lista)
                                        {
                                            if (ar.Manager.Equals(item.StaffName) && ar.Attendance_date.ToDateTime().Date == nn.Date)
                                            {

                                                if (ar != null)
                                                {
                                                    if (ar.BusinessHour != null && !"".Equals(ar.BusinessHour))
                                                    {
                                                        string time01 = ar.BusinessHour;//上班时间
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);


                                                        //将每日表中的上班时间和导入表中的签到时间进行比较                       
                                                        // 将时间字符串转换为DateTime类型
                                                        DateTime dateTime1 = DateTime.Parse(time01);
                                                        DateTime dateTime2 = DateTime.Parse(item.CheckInTime);
                                                        // 获取时间的时分秒部分
                                                        TimeSpan timeSpan1 = dateTime1.TimeOfDay;//上班时间
                                                        TimeSpan timeSpan2 = dateTime2.TimeOfDay;//签到时间
                                                                                                 // 比较两个时间的时分秒部分
                                                        int result = TimeSpan.Compare(timeSpan1, timeSpan2);
                                                        //如果上班时间大于签到时间，则将签到时间赋给上班时间
                                                        if (result > 0)
                                                        {
                                                            ar.BusinessHour = item.CheckInTime;
                                                            //TimeSpan difference = time2.Subtract(time1);
                                                            //string sql23 = "Update Attendance_Records set BusinessHour = @businessHour  where AttendanceId = @attendanceId ";
                                                            //dapper.ExcuteNonQuery(sql23, new { businessHour = person_time.BusinessHour, attendanceId = person_time.AttendanceId });

                                                        }
                                                        else
                                                        {
                                                            ar.ClosingHour = item.CheckInTime;//下班时间
                                                            ar.AttendanceLocation = item.CheckInLocation;//出勤地点       
                                                            TimeSpan difference = DateTime.Parse(ar.ClosingHour).Subtract(DateTime.Parse(ar.BusinessHour));
                                                            string differenceString = difference.ToString();
                                                            ar.AttendanceTime = differenceString;//出勤时间

                                                            //签到地点
                                                            string str = item.CheckInLocation;
                                                            Dictionary<string, string> address = Address(str);
                                                            if (address.ContainsKey("province"))
                                                            {//获取省的字符串
                                                                string province = address["province"];
                                                            }
                                                            if (address.ContainsKey("city"))
                                                            {//获取市的字符串
                                                                string city = address["city"];
                                                                ar.CheckInLocation = city;//签到地点
                                                            }
                                                            //if (address.ContainsKey("country"))
                                                            //{//获取区县的字符串
                                                            //    string country = address["country"];
                                                            //}
                                                            if (address.ContainsKey("community"))
                                                            {//获取具体位置的字符串
                                                                string community = address["community"];
                                                            }
                                                        }

                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.ClosingHour, x.AttendanceTime }, true);//更新
                                                        //string sql33 = "Update Attendance_Records set ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql33, new { closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                    else
                                                    {

                                                        ar.StaffAccount = item.StaffAccount;//员工账号

                                                        //a.Attendance_date = item.CheckInDate;//出勤日期

                                                        ar.AttendanceLocation = item.CheckInLocation;//出勤地点                

                                                        ar.BusinessHour = item.CheckInTime;//上班时间

                                                        ar.ClosingHour = item.CheckInTime;//下班时间

                                                        //出勤时间  
                                                        string time01 = ar.BusinessHour;
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);
                                                        TimeSpan difference = time2.Subtract(time1);
                                                        string differenceString = difference.ToString();
                                                        ar.AttendanceTime = differenceString;

                                                        ////签到地点
                                                        string str = item.CheckInLocation;
                                                        Dictionary<string, string> address = Address(str);
                                                        if (address.ContainsKey("province"))
                                                        {//获取省的字符串
                                                            string province = address["province"];
                                                        }
                                                        if (address.ContainsKey("city"))
                                                        {//获取市的字符串
                                                            string city = address["city"];
                                                            ar.CheckInLocation = city;//签到地点
                                                        }
                                                        //if (address.ContainsKey("country"))
                                                        //{//获取区县的字符串
                                                        //    string country = address["country"];
                                                        //}
                                                        if (address.ContainsKey("community"))
                                                        {//获取具体位置的字符串
                                                            string community = address["community"];
                                                        }

                                                        //添加签到信息
                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.StaffAccount, x.AttendanceLocation, x.BusinessHour, x.ClosingHour, x.CheckInLocation, x.AttendanceTime }, true);//更新
                                                        //string sql5 = "Update Attendance_Records set StaffAccount=@staffAccount,BusinessHour=@businessHour, ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql5, new { staffAccount = person_time.StaffAccount, businessHour = person_time.BusinessHour, closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                }
                                                break;
                                            }
                                        }




                                    }
                                }
                                dapper.BulkInsert<Attendance_Records>(lista);

                                ////更新分类及是否出差
                                //根据时间查出每日表中当前时间下所有用户的数据
                                string sql22 = "SELECT * from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                List<Attendance_Records> list22 = dapper.QueryList<Attendance_Records>(sql22, new { nowDateTime = nowDateTimen });
                                foreach (Holiday item2 in list2)
                                {
                                    DateTime time3 = DateTime.Parse(item2.StartTime);//假期开始时间
                                    DateTime time4 = DateTime.Parse(item2.EndTime);//假期结束时间
                                    int result1 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time3);
                                    int result2 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time4);
                                    if (result1 >= 0 && result2 <= 0)
                                    {
                                        flag = 1;//今天是休息日
                                    }
                                }

                                //遍历当前时间下的所有的用户数据
                                foreach (Attendance_Records item in list22)
                                {
                                    if (flag == 1)
                                    {
                                        //是休息日且签到地点不为空、出勤地点不在公司和青岛市则为出差
                                        if (!"".Equals(item.CheckInLocation) && item.CheckInLocation != null && !"青岛市".Equals(item.CheckInLocation) && !item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            item.Classify = 2;
                                            item.TravelStatus = "是";
                                        }
                                        else
                                        {
                                            item.Classify = 5;//休息
                                            item.TravelStatus = "否";
                                        }
                                    }


                                    else
                                    {
                                        if ("".Equals(item.CheckInLocation) || item.CheckInLocation == null)
                                        {
                                            item.Classify = 4;
                                            item.TravelStatus = "否";

                                        }
                                        else if ("青岛市".Equals(item.CheckInLocation) || item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            //打卡状态
                                            //1   请假   2   出差  3   出勤   4   未打卡 5   休息                      

                                            //a.Classify = 3;//出勤
                                            item.TravelStatus = "否";//是否出差
                                            item.Classify = 3;
                                        }
                                        else
                                        {
                                            item.TravelStatus = "是";
                                            item.Classify = 2;
                                        }
                                    }

                                    //string sql6 = "Update Attendance_Records set TravelStatus=@travelStatus,Classify=@classify where AttendanceId = @attendanceId ";
                                    //dapper.ExcuteNonQuery(sql6, new { travelStatus = item.TravelStatus, classify = item.Classify, attendanceId = item.AttendanceId });
                                    dapper.ExecuteScalar("UpdateAttendanceRecords", new { item.AttendanceId, item.CheckInLocation, item.TravelStatus, item.Classify }, System.Data.CommandType.StoredProcedure);

                                }



                            }
                        }
                        //不在当前月份之前
                        else
                        {
                            for (int i = 0; i < day; i++)
                            {
                                int ri = i + 1;
                                DateTime date = new DateTime(nian, yue, ri);
                                string formattedDate = date.ToString("yyyy-MM-dd");

                                int flag = 0;//今天是否为休息日
                                DateTime nn = (DateTime)formattedDate.ToDateTime();

                                string nowDaten = string.Format("{0:yyyy-MM}", nn) + "%";
                                string nowDateTimen = string.Format("{0:yyyy-MM-dd}", nn) + "%";

                                //从假期表中获取数据
                                string sql2 = "SELECT * FROM Holiday WHERE CONVERT(VARCHAR, StartTime, 23) LIKE @nowDaten";
                                List<Holiday> list2 = dapper.QueryList<Holiday>(sql2, new { nowDaten });

                                List<Attendance_Records> lista = new List<Attendance_Records>();


                                string delete_sql = "delete from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                dapper.ExcuteNonQuery(delete_sql, new { nowDateTime = nowDateTimen });

                                foreach (Division_Person item in listPerson)
                                {
                                    Attendance_Records a = new Attendance_Records();
                                    a.Division = item.Division;//销售事业部
                                    a.Manager = item.BusinessManager;//业务经理
                                    a.Attendance_date = date;//出勤日期                    
                                    lista.Add(a);
                                }

                                //根据导入表的数据对每日表进行更新
                                foreach (Statistical_Reports item in list)
                                {
                                    if (item.CheckInDate.ToDateTime() == nn)
                                    {
                                        foreach (Attendance_Records ar in lista)
                                        {
                                            if (ar.Manager.Equals(item.StaffName) && ar.Attendance_date.ToDateTime().Date == nn.Date)
                                            {

                                                if (ar != null)
                                                {
                                                    if (ar.BusinessHour != null && !"".Equals(ar.BusinessHour))
                                                    {
                                                        string time01 = ar.BusinessHour;//上班时间
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);


                                                        //将每日表中的上班时间和导入表中的签到时间进行比较                       
                                                        // 将时间字符串转换为DateTime类型
                                                        DateTime dateTime1 = DateTime.Parse(time01);
                                                        DateTime dateTime2 = DateTime.Parse(item.CheckInTime);
                                                        // 获取时间的时分秒部分
                                                        TimeSpan timeSpan1 = dateTime1.TimeOfDay;//上班时间
                                                        TimeSpan timeSpan2 = dateTime2.TimeOfDay;//签到时间
                                                                                                 // 比较两个时间的时分秒部分
                                                        int result = TimeSpan.Compare(timeSpan1, timeSpan2);
                                                        //如果上班时间大于签到时间，则将签到时间赋给上班时间
                                                        if (result > 0)
                                                        {
                                                            ar.BusinessHour = item.CheckInTime;
                                                            //TimeSpan difference = time2.Subtract(time1);
                                                            //string sql23 = "Update Attendance_Records set BusinessHour = @businessHour  where AttendanceId = @attendanceId ";
                                                            //dapper.ExcuteNonQuery(sql23, new { businessHour = person_time.BusinessHour, attendanceId = person_time.AttendanceId });

                                                        }
                                                        else
                                                        {
                                                            ar.ClosingHour = item.CheckInTime;//下班时间
                                                            ar.AttendanceLocation = item.CheckInLocation;//出勤地点       
                                                            TimeSpan difference = DateTime.Parse(ar.ClosingHour).Subtract(DateTime.Parse(ar.BusinessHour));
                                                            string differenceString = difference.ToString();
                                                            ar.AttendanceTime = differenceString;//出勤时间

                                                            //签到地点
                                                            string str = item.CheckInLocation;
                                                            Dictionary<string, string> address = Address(str);
                                                            if (address.ContainsKey("province"))
                                                            {//获取省的字符串
                                                                string province = address["province"];
                                                            }
                                                            if (address.ContainsKey("city"))
                                                            {//获取市的字符串
                                                                string city = address["city"];
                                                                ar.CheckInLocation = city;//签到地点
                                                            }
                                                            //if (address.ContainsKey("country"))
                                                            //{//获取区县的字符串
                                                            //    string country = address["country"];
                                                            //}
                                                            if (address.ContainsKey("community"))
                                                            {//获取具体位置的字符串
                                                                string community = address["community"];
                                                            }
                                                        }

                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.ClosingHour, x.AttendanceTime }, true);//更新
                                                        //string sql33 = "Update Attendance_Records set ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql33, new { closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                    else
                                                    {

                                                        ar.StaffAccount = item.StaffAccount;//员工账号

                                                        //a.Attendance_date = item.CheckInDate;//出勤日期

                                                        ar.AttendanceLocation = item.CheckInLocation;//出勤地点                

                                                        ar.BusinessHour = item.CheckInTime;//上班时间

                                                        ar.ClosingHour = item.CheckInTime;//下班时间

                                                        //出勤时间  
                                                        string time01 = ar.BusinessHour;
                                                        string time02 = ar.ClosingHour;
                                                        DateTime time1 = DateTime.Parse(time01);
                                                        DateTime time2 = DateTime.Parse(time02);
                                                        TimeSpan difference = time2.Subtract(time1);
                                                        string differenceString = difference.ToString();
                                                        ar.AttendanceTime = differenceString;

                                                        ////签到地点
                                                        string str = item.CheckInLocation;
                                                        Dictionary<string, string> address = Address(str);
                                                        if (address.ContainsKey("province"))
                                                        {//获取省的字符串
                                                            string province = address["province"];
                                                        }
                                                        if (address.ContainsKey("city"))
                                                        {//获取市的字符串
                                                            string city = address["city"];
                                                            ar.CheckInLocation = city;//签到地点
                                                        }
                                                        //if (address.ContainsKey("country"))
                                                        //{//获取区县的字符串
                                                        //    string country = address["country"];
                                                        //}
                                                        if (address.ContainsKey("community"))
                                                        {//获取具体位置的字符串
                                                            string community = address["community"];
                                                        }

                                                        //添加签到信息
                                                        //_attendanceRecordsRepository.DapperContext.Update(list1[0], x => new { x.StaffAccount, x.AttendanceLocation, x.BusinessHour, x.ClosingHour, x.CheckInLocation, x.AttendanceTime }, true);//更新
                                                        //string sql5 = "Update Attendance_Records set StaffAccount=@staffAccount,BusinessHour=@businessHour, ClosingHour = @closingHour,AttendanceTime = @attendanceTime,AttendanceLocation=@attendanceLocation,CheckInLocation = @checkInLocation where AttendanceId = @attendanceId ";
                                                        //dapper.ExcuteNonQuery(sql5, new { staffAccount = person_time.StaffAccount, businessHour = person_time.BusinessHour, closingHour = person_time.ClosingHour, attendanceTime = person_time.AttendanceTime, attendanceLocation = person_time.AttendanceLocation, checkInLocation = person_time.CheckInLocation, attendanceId = person_time.AttendanceId });
                                                    }
                                                }
                                                break;
                                            }
                                        }




                                    }
                                }
                                dapper.BulkInsert<Attendance_Records>(lista);

                                ////更新分类及是否出差
                                //根据时间查出每日表中当前时间下所有用户的数据
                                string sql22 = "SELECT * from Attendance_Records where CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDateTime";
                                List<Attendance_Records> list22 = dapper.QueryList<Attendance_Records>(sql22, new { nowDateTime = nowDateTimen });
                                foreach (Holiday item2 in list2)
                                {
                                    DateTime time3 = DateTime.Parse(item2.StartTime);//假期开始时间
                                    DateTime time4 = DateTime.Parse(item2.EndTime);//假期结束时间
                                    int result1 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time3);
                                    int result2 = DateTime.Compare(list22[0].Attendance_date.ToDateTime(), time4);
                                    if (result1 >= 0 && result2 <= 0)
                                    {
                                        flag = 1;//今天是休息日
                                    }
                                }

                                //遍历当前时间下的所有的用户数据
                                foreach (Attendance_Records item in list22)
                                {
                                    if (flag == 1)
                                    {
                                        //是休息日且签到地点不为空、出勤地点不在公司和青岛市则为出差
                                        if (!"".Equals(item.CheckInLocation) && item.CheckInLocation != null && !"青岛市".Equals(item.CheckInLocation) && !item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            item.Classify = 2;
                                            item.TravelStatus = "是";
                                        }
                                        else
                                        {
                                            item.Classify = 5;//休息
                                            item.TravelStatus = "否";
                                        }
                                    }


                                    else
                                    {
                                        if ("".Equals(item.CheckInLocation) || item.CheckInLocation == null)
                                        {
                                            item.Classify = 4;
                                            item.TravelStatus = "否";

                                        }
                                        else if ("青岛市".Equals(item.CheckInLocation) || item.CheckInLocation.Contains("达能环保设备公司"))
                                        {
                                            //打卡状态
                                            //1   请假   2   出差  3   出勤   4   未打卡 5   休息                      

                                            //a.Classify = 3;//出勤
                                            item.TravelStatus = "否";//是否出差
                                            item.Classify = 3;
                                        }
                                        else
                                        {
                                            item.TravelStatus = "是";
                                            item.Classify = 2;
                                        }
                                    }

                                    //string sql6 = "Update Attendance_Records set TravelStatus=@travelStatus,Classify=@classify where AttendanceId = @attendanceId ";
                                    //dapper.ExcuteNonQuery(sql6, new { travelStatus = item.TravelStatus, classify = item.Classify, attendanceId = item.AttendanceId });
                                    dapper.ExecuteScalar("UpdateAttendanceRecords", new { item.AttendanceId, item.CheckInLocation, item.TravelStatus, item.Classify }, System.Data.CommandType.StoredProcedure);

                                }



                            }
                        }
                    }
                        
                    
                }




                dapper.Commit();

            }


            catch (Exception ex)
            {
                dapper.Rollback();
                throw new Exception(ex.Message);

            }
        }

        public List<Attendance_Records> asd()
        {
            var dapper = DBServerProvider.SqlDapper;
            string sql11 = "SELECT * from Attendance_Records";
            List<Attendance_Records> list11 = dapper.QueryList<Attendance_Records>(sql11, true);
            return list11;
        }

        //地址
        public static Dictionary<string, string> Address(string address)

        {

            Dictionary<string, string> addres = new Dictionary<string, string>();

            if ("达能环保设备公司".Equals(address))
            {
                addres.Add("city", "青岛市");
                return addres;
            }
            else
            {
                while ((address.Contains("中国") && address.IndexOf("中国") == 0) || (address.Contains("中华人民共和国") && address.IndexOf("中华人民共和国") == 0))
                {
                    if (address.Contains("中国") && address.IndexOf("中国") == 0)
                    {

                        int ii = address.IndexOf("中国");
                        address = address.Substring(ii + 2, address.Length - ii - 2);

                    }
                    if (address.Contains("中华人民共和国") && address.IndexOf("中华人民共和国") == 0)
                    {

                        int ii = address.IndexOf("中华人民共和国");
                        address = address.Substring(ii + 7, address.Length - ii - 7);

                    }

                }




                string province = string.Empty;
                string city = string.Empty;
                string country = string.Empty;
                string community = string.Empty;

                int index = 0;

                StringBuilder stringBuilder = new StringBuilder();          //创建StringBuilder类对象
                string str = address;
                char[] array = str.ToCharArray();                //把字符串转化成字符数组
                IEnumerator enumerator = array.GetEnumerator();         //得到枚举器
                while (enumerator.MoveNext())                         //开始枚举
                {
                    if ((char)enumerator.Current != ' ')         //向StringBuilder类对象添加非空格字符
                        stringBuilder.Append(enumerator.Current.ToString());
                }
                string area = stringBuilder.ToString();


                ///将 省/自治区 分割出来
                if (area.Contains("省"))
                {
                    index = area.IndexOf("省");
                    province = area.Substring(0, index + 1);
                    addres.Add("province", province);
                    area = area.Substring(index + 1);
                }
                if (area.Contains("自治区"))
                {
                    index = area.IndexOf("自治区");
                    province = area.Substring(0, index + 3);
                    addres.Add("province", province);
                    area = area.Substring(index + 3);
                }




                string l2 = "";//名称
                int indexmin2 = area.Count();//位置


                //将 市 分割出来
                if (area.Contains("市"))
                {
                    index = area.IndexOf("市");
                    if (index < indexmin2)
                    {
                        indexmin2 = index;
                        l2 = "市";
                    }
                }
                if (area.Contains("自治州"))
                {
                    index = area.IndexOf("自治州");
                    if (index < indexmin2)
                    {
                        indexmin2 = index;
                        l2 = "自治州";
                    }
                }
                if (area.Contains("旗"))
                {
                    index = area.IndexOf("旗");
                    if (index < indexmin2)
                    {
                        indexmin2 = index;
                        l2 = "旗";
                    }
                }
                if (area.Contains("地区"))
                {
                    index = area.IndexOf("地区");
                    if (index < indexmin2)
                    {
                        indexmin2 = index;
                        l2 = "地区";
                    }
                }
                index = area.IndexOf("l2");
                city = area.Substring(0, indexmin2 + l2.Length);
                addres.Add("city", city);
                area = area.Substring(indexmin2 + l2.Length);




                string l3 = "";//名称
                int indexmin = area.Count();//位置


                if (area.Contains("市"))
                {
                    index = area.IndexOf("市");
                    if (index < indexmin)
                    {
                        indexmin = index;
                        l3 = "市";
                    }
                }

                if (area.Contains("县"))
                {
                    index = area.IndexOf("县");
                    if (index < indexmin)
                    {
                        indexmin = index;
                        l3 = "县";
                    }
                }
                if (area.Contains("旗"))
                {
                    index = area.IndexOf("旗");
                    if (index < indexmin)
                    {
                        indexmin = index;
                        l3 = "旗";
                    }
                }
                if (area.Contains("区"))
                {
                    index = area.IndexOf("区");
                    if (index < indexmin)
                    {
                        indexmin = index;
                        l3 = "区";
                    }
                }
                if (l3.Length > 0)
                {
                    country = area.Substring(0, indexmin + 1);
                    area = area.Substring(indexmin + 1);
                    addres.Add("country", country);
                    community = area;
                    addres.Add("community", community);
                }


                return addres;


            }



        }


    }
}
